<?php
/**
 * Description of Emms_Cron_DonationAllocations
 *
 * @author andres
 */
class Emms_Cron_DonationAllocations extends Emms_Cron implements Emms_Interface_Cron
{
    private $_allocations;
    private $_sponsors;
    private $_masters;
    
    public function __construct() 
    {
        parent::__construct();
        
        try {
            $this->_allocations = new Zend_Db_Table(array(
                'name' => 'donation_allocations', 
                'primary' => 'DonationAllocationId'));
            $this->_sponsors = $this->_getSponsors();
            $this->_masters = $this->_getMasters();
            
        } catch  (Zend_Exception $e) {
            throw $e;
        }
    }
    
    public function update()
    {
        $this->db->beginTransaction();
        try {
            foreach($this->_masters as $lm) {
                while(($lm['Pending'] > 0)&&($pid = $this->_getMaxPriorityId($this->_sponsors,$lm['LoanMasterId']))) {
                    $k = $this->_sponsors[$pid]['Donations'] - $this->_sponsors[$pid]['WriteOffs'];
                    if($this->_sponsors[$pid]['Funds'] > 0) {
                        $amt = min($lm['Pending'],$this->_sponsors[$pid]['Funds']);
                        $lm['Pending'] = $lm['Pending'] - $amt;
                        $this->_sponsors[$pid]['Balance'] = $this->_sponsors[$pid]['Balance'] + $amt;
                        $this->_sponsors[$pid]['Funds'] = $this->_sponsors[$pid]['Funds'] - $amt;
                        $this->_sponsors[$pid]['Priority'] = 1 - ($this->_sponsors[$pid]['Balance']/$k);
                        $this->_allocations->insert(array(
                            'LoanMasterId' => $lm['LoanMasterId'],
                            'SponsorId' => $pid,
                            'Donation' => $amt,
                            'Datetime' => $this->datetime
                        ));
                    }
                }
            }
        } catch (Zend_Exception $e) {
            
            $this->db->rollBack();
            throw $e;
        }
        $this->db->commit();
    }
    
    private function _getSponsors() 
    {
        $sps = $this->db->fetchAssoc('
            select 
                da.SponsorId,
                dn.Donations,
                round(sum(if(al.BalancePrincipal,al.BalancePrincipal*(da.Donation/lm.Amount),0)),2) Balance,
                round(sum(if(lwo.Principal,lwo.Principal*(da.Donation/lm.Amount),0)),2) WriteOffs
            from (donation_allocations da, loan_masters lm, loans l)
            left join active_loans al on al.LoanId = l.LoanId
            left join loan_write_offs lwo on lwo.LoanId = l.LoanId
            left join (select SponsorId, sum(ConvAmount) Donations from donations group by SponsorId) dn on dn.SponsorId = da.SponsorId
            where
                lm.Status = "Disbursed" and
                lm.LoanMasterId = da.LoanMasterId and
                l.LoanMasterId = da.LoanMasterId
            group by da.SponsorId');
        
        $sp = new Zend_Db_Table(array('name' => 'view_sponsors', 'primary' => 'SponsorId'));
        $sponsors = $sp->fetchAll();

        $s = array();
        foreach($sponsors as $sponsor) {
            $donations = $sps[$sponsor->SponsorId]['Donations'];
            $balance = $sps[$sponsor->SponsorId]['Balance'];
            $woffs = $sps[$sponsor->SponsorId]['WriteOffs'];
            $funds = $donations - $balance - $woffs;
            if($funds > 0) {
                $s[$sponsor->SponsorId]['WomenOnly'] = $sponsor['WomenOnly'];
                $s[$sponsor->SponsorId]['Donations'] = $donations;
                $s[$sponsor->SponsorId]['Balance']   = $balance;
                $s[$sponsor->SponsorId]['WriteOffs'] = $woffs;
                $s[$sponsor->SponsorId]['Funds']     = $funds;
                $s[$sponsor->SponsorId]['Priority']  = 1 - ($balance/($donations-$woffs));        
            }
        }
        return $s; 
    }

    private function _getMasters()
    { 
        return $this->db->fetchAssoc('
            select distinct 
                lm.LoanMasterId, 
                1 + ? - datediff(?,ckd.Date) TimeLeft, 
                coalesce(rc.Donations,0.00) Donations, 
                lm.Amount - coalesce(rc.Donations,0.00) Pending,
                lm.LocationId
            from 
            (
                loan_masters lm, programs p,
                (
                    select l.LoanMasterId, lsc.Date 
                    from loan_status_changes lsc, loans l 
                    where 
                        l.LoanId = lsc.LoanId and 
        	        lsc.Status = "Disbursed" and 
        	        lsc.Date = date_add(?, interval -(? + 1) day)
                ) ckd 
            ) 
            left join 
            (
                select LoanMasterId, sum(Donation) Donations from donation_allocations group by LoanMasterId
            ) rc on rc.LoanMasterId = lm.LoanMasterId 
            where 
                lm.BorrowerType = "B" and 
                lm.Status = "Disbursed" and 
                p.ProgramId = lm.ProgramId and
                p.PublicSponsorship = "Yes" and
                ckd.LoanMasterId = lm.LoanMasterId and 
                coalesce(rc.Donations,0.00) < lm.Amount 
            order by 
                lm.LoanMasterId',array($this->conf->param->sponsorship->auto_sp_margin,
                    $this->date,$this->date,$this->conf->param->sponsorship->auto_sp_margin));
    }
    
    /*
     * Includes support for the WomenOnly sponsor field
     */
    private function _getMaxPriorityId($s,$loanMasterId)
    {
        $male = $this->db->fetchAll('select c.Gender from loans l, clients c 
            where l.LoanMasterId = ? and c.ClientId = l.ClientId and c.Gender = "Male"', 
                $loanMasterId);
    
        $pid = key($s);
        $pval = $s[$pid]['Priority'];
        foreach($s as $id => $data) {
            if ($data['Priority'] > $pval) { 
                if(($data['WomenOnly'] == 'No' )||(count($male) == 0)) {
                    $pid = $id;
                    $pval = $data['Priority'];	
                }
            }
        }
        return $pid;
    }
}



